from odoo import fields, models


class ProductionMaterialDemandLine(models.Model):
    _name = 's.production.material.demand.line'
    _description = 'Production Material Demand Line'
    _auto = False

    finished_product_id = fields.Many2one('product.product', string='Finished Product')
    material_product_id = fields.Many2one('product.product', string='Material Product')
    qty = fields.Float('Quantity', digits='Product Unit of Measure')
    need_qty = fields.Float('Need Quantity', digits='Product Unit of Measure', compute='_compute_need_qty')
    uom_id = fields.Many2one('uom.uom', string='Uom', related='material_product_id.uom_id')

    def _compute_need_qty(self):
        for rec in self:
            rec.need_qty = rec.qty - rec.material_product_id.pre_production_qty

    @property
    def _table_query(self):
        company_id = self.env.user.company_id
        material_product_id = self._context.get('material_product_id')
        where = ''
        if material_product_id:
            where = f' and pp.id = {material_product_id}'

        return f"""
            select row_number() OVER() AS id,
                mp.product_id finished_product_id,
                sm.product_id material_product_id, 
                sum(sm.product_qty) qty
            from stock_move sm
                     left join product_product pp on sm.product_id = pp.id
                     left join product_template pt on pp.product_tmpl_id = pt.id
                     left join uom_uom uu on sm.product_uom = uu.id
                     left join stock_location sl on sm.location_id = sl.id
                     left join stock_location sl2 on sm.location_dest_id = sl2.id
                     left join product_category pc on pt.categ_id = pc.id
                     left join product_category pc2 on pc2.id = pc.parent_id
            left join mrp_production mp on sm.raw_material_production_id = mp.id
            
            where sl.barcode = 'WH-PREPRODUCTION'
              and sl2.usage = 'production'
              and sm.state not in ('done', 'cancel')
              AND pc.code NOT IN ('CHP', 'FYL', 'BCP')   -- 排除子类
              AND (pc2.code IS NULL OR pc2.code != 'CR') -- 排除父类 'CR'
              and sm.company_id = {company_id.id}
              {where}
            group by mp.product_id,sm.product_id
        
        """
